import json

from crawler.connection.MySQL import MySQLConnection
from crawler.logger.log import logger

"""执行语句查询有结果返回结果没有返回0；增/删/改返回变更数据条数，没有返回0"""


class MySqLHelper(MySQLConnection):

    def insert_by_index(self):
        """
        使用下标索引的方式插入数据
        """
        sql = "insert into user(id,name) values(%s,%s)"
        # 获取数据库连接
        try:

            # 执行插入动作
            self.insert(sql, ("3", "张三"))
            # 提交
            self.connect.commit()
        except Exception as e:
            logger.exception(e)

    def insert_batch_by_index(self):
        """
        使用下标索引的方式批量插入数据
        """
        sql = "insert into user(id,name) values(%s,%s)"

        try:
            # 执行批量插入动作
            data = [("1", "张三"), ("2", "张三2")]
            # 放入的是元祖
            self.batch(sql, data)
            # 提交
            self.connect.commit()
        except Exception as e:
            logger.exception(e)

    def insert_by_dict(self):
        """
        使用下标索引的方式插入数据
        """
        sql = "insert into user(id,name) values(%(id)s,%(name)s)"

        try:
            # 执行插入动作
            # 此时我们使用的是字典
            self.insert(sql, {"name": "张三", "id": "1"})
            # 提交,必须
            self.commit()
        except Exception as e:
            logger.exception(e)

    def insert_batch_by_dict(self):
        """
        使用下标索引的方式批量插入数据
        """
        sql = "insert into user(id,name) values(%(id)s,%(name)s)"

        try:
            # 执行批量插入动作
            data = [{"name": "张三", "id": "2"}, {"name": "张三1", "id": "3"}]
            # 放入的是自字典
            self.batch(sql, data)
            # 提交
            self.commit()
        except Exception as e:
            logger.exception(e)

    def query_page(self):
        """
        分页查询返回分页对象,是一个字典对象，主要包含属性：

        current_page:当前页码
        page_size:当前页码大小
        count:当前统计SQL语句的表记录总数
        total_page:当前总页码数
        data:当前的数据集合,是一个字典集合对象
        不分页的查询返回的是data集合字典对象，和分页使用方法类似
        :return:
        """
        sql = "select id, dio, createBy from ArticleDioList "

        try:
            page = 1
            size = 500
            pagination = self.listByPage(sql, page, size, )
            return pagination

        except Exception as e:
            logger.exception(e)

    def run(self):
        # self.insert_batch_by_index()
        # self.insert_by_index()
        # self.insert_by_dict()
        # self.insert_batch_by_dict()
        res = self.query_page()
        print(json.dumps(res))
        # 必须关闭数据库
        self.close()


if __name__ == '__main__':
    mh = MySqLHelper()
    mh.run()
